VERSION 5.00
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCTL.OCX"
Object = "{B3FB64BF-91F9-11D7-A482-0008A14158BC}#2.22#0"; "ITGControls.ocx"
Begin VB.Form frmARCollectionList 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Sales Invoice List (Receipt)"
   ClientHeight    =   4620
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   4680
   BeginProperty Font 
      Name            =   "Tahoma"
      Size            =   8.25
      Charset         =   0
      Weight          =   400
      Underline       =   0   'False
      Italic          =   0   'False
      Strikethrough   =   0   'False
   EndProperty
   Icon            =   "frmARCollectionList.frx":0000
   KeyPreview      =   -1  'True
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   4620
   ScaleWidth      =   4680
   StartUpPosition =   1  'CenterOwner
   Begin VB.Frame Frame1 
      Height          =   4545
      Left            =   60
      TabIndex        =   8
      Top             =   60
      Width           =   4545
      Begin ITGControls.ITGTextBox txtReturns 
         Height          =   285
         Left            =   1980
         TabIndex        =   3
         Top             =   2265
         Width           =   2385
         _ExtentX        =   3995
         _ExtentY        =   503
         SendKeysTab     =   -1  'True
         BackColor       =   14737632
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Text            =   "0"
         DataType        =   1
         DecimalPlace    =   2
         Locked          =   -1  'True
         Label           =   "Returns"
         BeginProperty LabelFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         LabelWidth      =   1000
         TextBoxWidth    =   1325
      End
      Begin MSComctlLib.ListView lvwList 
         Height          =   4455
         Left            =   0
         TabIndex        =   9
         Top             =   90
         Width           =   1695
         _ExtentX        =   2990
         _ExtentY        =   7858
         View            =   3
         LabelWrap       =   -1  'True
         HideSelection   =   -1  'True
         FullRowSelect   =   -1  'True
         GridLines       =   -1  'True
         HotTracking     =   -1  'True
         _Version        =   393217
         ForeColor       =   -2147483640
         BackColor       =   -2147483643
         BorderStyle     =   1
         Appearance      =   0
         NumItems        =   1
         BeginProperty ColumnHeader(1) {BDD1F052-858B-11D1-B16A-00C0F0283628} 
            Text            =   "Reference No."
            Object.Width           =   2540
         EndProperty
      End
      Begin ITGControls.ITGTextBox txtSMCode 
         Height          =   285
         Left            =   1980
         TabIndex        =   0
         Top             =   1065
         Width           =   2415
         _ExtentX        =   4048
         _ExtentY        =   503
         SendKeysTab     =   -1  'True
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         AllCaps         =   -1  'True
         Mandatory       =   -1  'True
         Locked          =   -1  'True
         Label           =   "Salesman"
         BeginProperty LabelFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         LabelWidth      =   1000
         TextBoxWidth    =   1355
      End
      Begin ITGControls.ITGDateBox dtbDate 
         Height          =   285
         Left            =   3015
         TabIndex        =   1
         Tag             =   "Order Date"
         Top             =   1350
         Width           =   1365
         _ExtentX        =   2408
         _ExtentY        =   503
         Enabled         =   0   'False
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Text            =   ""
         SendKeysTab     =   -1  'True
         Mandatory       =   -1  'True
         DatePicker      =   0   'False
      End
      Begin ITGControls.ITGLabel ITGLabel4 
         Height          =   285
         Left            =   1980
         TabIndex        =   11
         Top             =   1350
         Width           =   915
         _ExtentX        =   1614
         _ExtentY        =   503
         Caption         =   "Ref. Date"
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
      End
      Begin ITGControls.ITGTextBox txtGross 
         Height          =   285
         Left            =   1980
         TabIndex        =   2
         Top             =   1965
         Width           =   2385
         _ExtentX        =   3995
         _ExtentY        =   503
         SendKeysTab     =   -1  'True
         BackColor       =   14737632
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Text            =   "0"
         DataType        =   1
         DecimalPlace    =   2
         Locked          =   -1  'True
         Label           =   "Gross"
         BeginProperty LabelFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         LabelWidth      =   1000
         TextBoxWidth    =   1325
      End
      Begin ITGControls.ITGTextBox txtPayment 
         Height          =   285
         Left            =   1980
         TabIndex        =   4
         Top             =   2565
         Width           =   2385
         _ExtentX        =   3995
         _ExtentY        =   503
         SendKeysTab     =   -1  'True
         BackColor       =   14737632
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Text            =   "0"
         DataType        =   1
         DecimalPlace    =   2
         Locked          =   -1  'True
         Label           =   "Payment"
         BeginProperty LabelFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         LabelWidth      =   1000
         TextBoxWidth    =   1325
      End
      Begin ITGControls.ITGTextBox txtDebit 
         Height          =   285
         Left            =   1980
         TabIndex        =   5
         Top             =   2865
         Width           =   2370
         _ExtentX        =   3969
         _ExtentY        =   503
         SendKeysTab     =   -1  'True
         BackColor       =   14737632
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Text            =   "0"
         DataType        =   1
         DecimalPlace    =   2
         Locked          =   -1  'True
         Label           =   "Debit"
         BeginProperty LabelFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         LabelWidth      =   1000
         TextBoxWidth    =   1310
      End
      Begin ITGControls.ITGTextBox txtCredit 
         Height          =   285
         Left            =   1980
         TabIndex        =   6
         Top             =   3165
         Width           =   2385
         _ExtentX        =   3995
         _ExtentY        =   503
         SendKeysTab     =   -1  'True
         BackColor       =   14737632
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Text            =   "0"
         DataType        =   1
         DecimalPlace    =   2
         Locked          =   -1  'True
         Label           =   "Credit"
         BeginProperty LabelFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         LabelWidth      =   1000
         TextBoxWidth    =   1325
      End
      Begin ITGControls.ITGTextBox txtBalance 
         Height          =   285
         Left            =   1980
         TabIndex        =   7
         Top             =   3465
         Width           =   2385
         _ExtentX        =   3995
         _ExtentY        =   503
         SendKeysTab     =   -1  'True
         BackColor       =   14737632
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Text            =   "0"
         DataType        =   1
         DecimalPlace    =   2
         Locked          =   -1  'True
         Label           =   "Balance"
         BeginProperty LabelFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         LabelWidth      =   1000
         TextBoxWidth    =   1325
      End
      Begin ITGControls.ITGTextBox txtTPCGross 
         Height          =   285
         Left            =   1980
         TabIndex        =   13
         Top             =   1965
         Visible         =   0   'False
         Width           =   2385
         _ExtentX        =   3995
         _ExtentY        =   503
         SendKeysTab     =   -1  'True
         BackColor       =   14737632
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Text            =   "0"
         DataType        =   1
         DecimalPlace    =   2
         Locked          =   -1  'True
         Label           =   "TPC Gross"
         BeginProperty LabelFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         LabelWidth      =   1000
         TextBoxWidth    =   1325
      End
      Begin ITGControls.ITGTextBox txtTPCBalance 
         Height          =   285
         Left            =   1980
         TabIndex        =   14
         Top             =   2265
         Width           =   2385
         _ExtentX        =   3995
         _ExtentY        =   503
         SendKeysTab     =   -1  'True
         BackColor       =   14737632
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Text            =   "0"
         DataType        =   1
         DecimalPlace    =   2
         Locked          =   -1  'True
         Label           =   "TPC Balance"
         BeginProperty LabelFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         LabelWidth      =   1000
         TextBoxWidth    =   1325
      End
      Begin ITGControls.ITGCommandButton cmdCancel 
         Height          =   675
         Left            =   3135
         TabIndex        =   15
         Top             =   3825
         Width           =   1290
         _ExtentX        =   2275
         _ExtentY        =   1191
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Caption         =   "&Cancel"
      End
      Begin ITGControls.ITGCommandButton cmdOK 
         Height          =   675
         Left            =   1860
         TabIndex        =   16
         Top             =   3825
         Width           =   1290
         _ExtentX        =   2275
         _ExtentY        =   1191
         BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   400
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Caption         =   "&OK"
      End
      Begin VB.Line Line1 
         X1              =   2505
         X2              =   4335
         Y1              =   1785
         Y2              =   1785
      End
      Begin VB.Label Label4 
         AutoSize        =   -1  'True
         BackStyle       =   0  'Transparent
         Caption         =   "Details"
         BeginProperty Font 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         ForeColor       =   &H00000000&
         Height          =   195
         Index           =   2
         Left            =   1740
         TabIndex        =   12
         Top             =   1680
         Width           =   585
      End
      Begin VB.Label lblClient 
         Alignment       =   2  'Center
         Caption         =   "Client"
         BeginProperty Font 
            Name            =   "Tahoma"
            Size            =   8.25
            Charset         =   0
            Weight          =   700
            Underline       =   0   'False
            Italic          =   0   'False
            Strikethrough   =   0   'False
         EndProperty
         Height          =   735
         Left            =   1800
         TabIndex        =   10
         Top             =   210
         Width           =   2610
      End
   End
End
Attribute VB_Name = "frmARCollectionList"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Enum eSIPick
    ToCollection
    ToDebitCredit
    ToDebitCreditPR  'AP adjustment purchase return
    ToDebitCreditSR  'AR adjustment sales return
    ToDebitCreditWRR
    ToDebitCreditWRR_Voucher
    ToVoucherWRR    'Voucher trade wrr list
    ToVoucherTPC  'Invoices with TPC
    ToIssuance  'List of Vouchers
    ToRFPWRR    'RFP trade wrr list
    ToDDRR
    ToVoucherPI 'Purchase Invoice to Voucher Trade
    ToRFPPI     'RFP trade wrr list
    ToDebitCreditPI_Voucher 'PI Adjustment
    ToSOE    'for PMS_EXPENSE
End Enum

Public mSIPick As eSIPick
Public mcInvNo As String
Public mdDate As String
Public mnBalance As Double
Public mnTPCBalance As Double
Public mnEWTrate As Double


Private connList As New ADODB.Connection

Private Sub cmdCancel_Click()
    Unload Me
    Set frmARCollectionList = Nothing
End Sub

Private Sub cmdOK_Click()
    If lvwList.ListItems.Count = 0 Then
        Unload Me
        Exit Sub
    End If
    
    Select Case mSIPick
        Case ToDebitCredit, ToDebitCreditSR, ToDebitCreditPR, ToDebitCreditWRR, ToDDRR, ToDebitCreditWRR_Voucher, ToDebitCreditPI_Voucher, ToSOE
        Case ToVoucherTPC, ToIssuance
            FrmName.AddDetailsFromList mcInvNo, mdDate, mnTPCBalance
        Case ToVoucherPI
            FrmName.AddDetailsFromList mcInvNo, mdDate, mnBalance, mnEWTrate
        Case Else
            FrmName.AddDetailsFromList mcInvNo, mdDate, mnBalance
    End Select
    Unload Me
End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)
    If KeyAscii = vbKeyEscape Then
        Unload Me
        Set frmARCollectionList = Nothing
    End If
End Sub

Private Sub Form_Load()
Dim rsSIList As New Recordset

    Select Case mSIPick
        Case ToDebitCreditSR
            sSQL = "SELECT DISTINCT cRefNo AS cInvNo FROM WRR_T WHERE cWRRNo = '" & Trim(cString) & "' AND cCompanyID = '" & COID & "'"
            Me.Caption = "Sales Invoice List (Sales Return)"
        Case ToDebitCreditPR
            sSQL = "SELECT DISTINCT cRefNo AS cInvNo FROM DR_T WHERE cDRNo = '" & Trim(cString) & "' AND cCompanyID = '" & COID & "'"
            Me.Caption = "Delivery Receipt List (Purchase return)"
        Case ToDebitCreditWRR
            txtSMCode.Visible = False
            sSQL = "SELECT A.cWRRNo AS cInvNo FROM WRR A " & _
                    "INNER JOIN V_WRRBalanceInquiry B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
                    "WHERE A.cCode = '" & Trim(RepName) & "' " & _
                    "AND A.cCompanyID = '" & COID & "' AND A.lCancelled = 0 " & _
                    "AND ((B.nGross - B.nPaid - B.nReturns + B.nCredit - B.nDebit) <> 0)"
            Me.Caption = "Warehouse Receiving List"
        Case ToDebitCreditWRR_Voucher
            txtSMCode.Visible = False
            sSQL = "SELECT A.cWRRNo AS cInvNo FROM WRR A " & _
                    "INNER JOIN V_WRRBalanceInquiry_Voucher B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
                    "WHERE A.cCode = '" & Trim(RepName) & "' " & _
                    "AND A.cCompanyID = '" & COID & "' AND A.lCancelled = 0 " & _
                    "AND ((B.nGross - B.nPaid - B.nReturns + B.nCredit - B.nDebit) <> 0)"
            Me.Caption = "Warehouse Receiving List"
        Case ToVoucherWRR
            txtSMCode.Visible = False
            sSQL = "SELECT A.cWRRNo AS cInvNo FROM WRR A " & _
                    "INNER JOIN V_WRRBalanceInquiry_Voucher B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
                    "WHERE A.cCode = '" & Trim(RepName) & "' " & _
                    "AND A.cCompanyID = '" & COID & "' AND A.lCancelled = 0 " & _
                    "AND ((B.nGross - B.nPaid - B.nReturns + B.nCredit - B.nDebit) <> 0)"
            Me.Caption = "Warehouse Receiving List"
        Case ToRFPWRR
            txtSMCode.Visible = False
            sSQL = "SELECT A.cWRRNo AS cInvNo FROM WRR A " & _
                    "INNER JOIN V_WRRBalanceInquiry B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
                    "WHERE A.cCode = '" & Trim(RepName) & "' " & _
                    "AND A.cCompanyID = '" & COID & "' AND A.lCancelled = 0 " & _
                    "AND ((B.nGross - B.nPaid - B.nReturns + B.nCredit - B.nDebit) <> 0)"
            Me.Caption = "Warehouse Receiving List"
        Case ToVoucherTPC
            txtSMCode.Visible = False
            sSQL = "SELECT A.cInvNo FROM SALES A " & _
                    "INNER JOIN V_TPCBalanceInquiry B ON A.cInvNo = B.cInvNo AND A.cCompanyID = B.cCompanyID " & _
                    "WHERE A.cCode = '" & Trim(RepName) & "' " & _
                    "AND A.cCompanyID = '" & COID & "' " & _
                    "AND ((B.nTPCGross - B.nPaid) <> 0) AND A.lCancelled = 0"
            Me.Caption = "Sales Invoice List (with TPC)"
        Case ToIssuance
            txtSMCode.Visible = False
            sSQL = "SELECT A.cTranNo AS cInvNo FROM VOUCHER A " & _
                    "WHERE A.cCompanyID = '" & sUnitId & "' " & _
                    "AND ((A.nTDebit - A.nTCredit - A.nEWT - nEVAT) <> 0) AND A.lCancelled = 0"
            Me.Caption = "Voucher List"
        
        Case ToVoucherPI
            txtSMCode.Visible = False
'            sSQL = "SELECT a.cTranNo AS cInvNo " & _
                    "FROM purchase a " & _
                    "    INNER JOIN V_PIBalanceInquiry_Voucher B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID " & _
                    " WHERE A.cCode = '" & Trim(RepName) & "' AND A.cCompanyID = '" & COID & "' " & _
                    "    AND A.lCancelled = 0 AND ((B.nGross - B.nPaid - B.nReturns + B.nCredit - B.nDebit) <> 0)"
                    
             sSQL = "exec sp_PIBalanceInquiry_AP_lst_inv '" & Trim(RepName) & "', '" & COID & "'"      'Code by geeno valeriano
             
'            sSQL = "SELECT A.cWRRNo AS cInvNo FROM WRR A " & _
'                    "INNER JOIN V_WRRBalanceInquiry_Voucher B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
'                    "WHERE A.cCode = '" & Trim(RepName) & "' " & _
'                    "AND A.cCompanyID = '" & COID & "' AND A.lCancelled = 0 " & _
'                    "AND ((B.nGross - B.nPaid - B.nReturns + B.nCredit - B.nDebit) <> 0)"
            Me.Caption = "Purchase Invoice List"
        
        Case ToRFPPI
            txtSMCode.Visible = False
            sSQL = "SELECT A.cTranNo AS cInvNo " & _
                   " FROM PURCHASE A " & _
                   " INNER JOIN V_PIBalanceInquiry B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID " & _
                   " WHERE A.cCode = '" & Trim(RepName) & "' AND A.cCompanyID =  '" & COID & "' AND A.lCancelled = 0 AND ((B.nGross - B.nPaid - B.nReturns + B.nCredit - B.nDebit) <> 0)"

'            sSQL = "SELECT A.cWRRNo AS cInvNo FROM WRR A " & _
'                    "INNER JOIN V_WRRBalanceInquiry B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
'                    "WHERE A.cCode = '" & Trim(RepName) & "' " & _
'                    "AND A.cCompanyID = '" & COID & "' AND A.lCancelled = 0 " & _
'                    "AND ((B.nGross - B.nPaid - B.nReturns + B.nCredit - B.nDebit) <> 0)"
            Me.Caption = "Warehouse Receiving List"
        Case ToDebitCreditPI_Voucher
            txtSMCode.Visible = False
'            sSQL = "SELECT A.cTranNo AS cInvNo " & _
                   " FROM PURCHASE A " & _
                   " INNER JOIN V_PIBalanceInquiry_Voucher B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID " & _
                   " WHERE A.cCode = '" & Trim(RepName) & "' AND A.cCompanyID = '" & COID & "' AND A.lCancelled = 0 AND ((B.nGross - B.nPaid - B.nReturns + B.nCredit - B.nDebit) <> 0)"
            
            sSQL = "exec sp_PIBalanceInquiry_AP_lst_inv '" & Trim(RepName) & "', '" & COID & "'"      'Code by geeno valeriano
'
'            sSQL = "SELECT A.cWRRNo AS cInvNo FROM WRR A " & _
'                    "INNER JOIN V_WRRBalanceInquiry_Voucher B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
'                    "WHERE A.cCode = '" & Trim(RepName) & "' " & _
'                    "AND A.cCompanyID = '" & COID & "' AND A.lCancelled = 0 " & _
'                    "AND ((B.nGross - B.nPaid - B.nReturns + B.nCredit - B.nDebit) <> 0)"
            Me.Caption = "Warehouse Receiving List"
            
            Case ToSOE
            txtSMCode.Visible = False
            'sSQL = "SELECT A.cTranNo AS cInvNo FROM VOUCHER A " & _
                    "WHERE A.cCompanyID = '" & COID & "' AND A.lApproved = 1 " & _
                    "AND A.lCancelled = 0 AND A.cCode = '" & RepName & "' AND A.cType = 'Others'"
            
            sSQL = "Exec sp_PMSVoucherBalanceInquiry '" & Trim(RepName) & "' ,'" & COID & "'"
             
            Me.Caption = "Voucher List"
        
    
        Case Else
'            sSQL = "SELECT A.cInvNo FROM SALES A " & _
'                    "INNER JOIN V_SalesBalanceInquiry B ON A.cInvNo = B.cInvNo AND A.cCompanyID = B.cCompanyID " & _
'                    "WHERE A.cCode = '" & Trim(RepName) & "' " & _
'                    "AND A.cCompanyID = '" & COID & "' AND A.lCancelled = 0 " & _
'                    "AND ((B.nSales - B.nPaid - B.nReturns - B.nCredit + B.nDebit) <> 0)"
            sSQL = "SELECT A.cInvNo " & _
                "FROM SALES A " & _
                "INNER JOIN V_SalesBalanceInquiry B ON A.cInvNo = B.cInvNo AND A.cCompanyID = B.cCompanyID " & _
                "WHERE A.cCompanyID = '" & COID & "' AND ((B.nSales - B.nPaid - B.nReturns - B.nCredit + B.nDebit) <> 0) " & _
                    "AND A.cCode = '" & Trim(RepName) & "' " & _
                "UNION ALL " & _
                "SELECT A.cInvNo " & _
                "FROM SALES A " & _
                "INNER JOIN V_SalesBalanceInquiry B ON A.cInvNo = B.cInvNo AND A.cCompanyID = B.cCompanyID " & _
                "LEFT OUTER JOIN CLIENT_CUSTOMER C ON A.cCompanyID = C.cCompanyID AND A.cCode = C.cCode " & _
                "WHERE A.cCompanyID = '" & COID & "' AND ((B.nSales - B.nPaid - B.nReturns - B.nCredit + B.nDebit) <> 0) " & _
                    "AND A.cCode <> '" & Trim(RepName) & "' AND C.cGroupCode = '" & Trim(RepName) & "' "
            Me.Caption = "Sales Invoice List"
    End Select
    
    OpenNewConnection connList
    
    rsSIList.Open sSQL, connList, adOpenForwardOnly, adLockReadOnly
    
    lvwList.ListItems.Clear

    Do Until rsSIList.EOF
        Set itmX = lvwList.ListItems.Add(, , Trim(rsSIList!cInvNo))
        rsSIList.MoveNext
    Loop
    
    rsSIList.Close
    Set rsSIList = Nothing
    
    lblClient = RepName & " - " & GetValueFrTable("cName", "CLIENT_Supplier", "cCode = '" & Trim(RepName) & "'")
    
    If lvwList.ListItems.Count <> 0 Then lvwList_ItemClick lvwList.ListItems(lvwList.SelectedItem.Index)

End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set connList = Nothing
    RepName = ""
    cString = ""
End Sub

Private Sub lblClient_Click()

End Sub

Private Sub lvwList_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
    lvwList.Sorted = True
    lvwList.SortKey = ColumnHeader.Index - 1
End Sub

Private Sub lvwList_ItemClick(ByVal Item As MSComctlLib.ListItem)
    If lvwList.ListItems.Count = 0 Then Exit Sub
    mcInvNo = Trim(lvwList.SelectedItem)
    ListInvDetail (Trim(lvwList.SelectedItem))
End Sub

'Show invoice details
Sub ListInvDetail(InvNo As String)
On Error Resume Next
Dim rsList As New Recordset

    Select Case mSIPick
        Case ToDebitCreditPR
            sSQL = "SELECT A.*, A.nGross AS nSales , (A.nGross - A.nPaid - A.nReturns + A.nCredit - A.nDebit) AS nBalance, '' AS cSMan, B.dDate FROM V_WRRBalanceInquiry A " & _
                    "LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
                    "WHERE A.cWRRNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "'"
        Case ToDebitCreditWRR
            sSQL = "SELECT A.*, A.nGross AS nSales, (A.nGross - A.nPaid - A.nReturns + A.nCredit - A.nDebit) AS nBalance, '' AS cSMan, B.dDate FROM V_WRRBalanceInquiry A " & _
                    "LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
                    "WHERE A.cWRRNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "'"
        Case ToDebitCreditWRR_Voucher
            sSQL = "SELECT A.*, A.nGross AS nSales, (A.nGross - A.nPaid - A.nReturns + A.nCredit - A.nDebit) AS nBalance, '' AS cSMan, B.dDate FROM V_WRRBalanceInquiry_Voucher A " & _
                    "LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
                    "WHERE A.cWRRNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "'"
        Case ToVoucherWRR
            sSQL = "SELECT A.*, A.nGross AS nSales, (A.nGross - A.nPaid - A.nReturns + A.nCredit - A.nDebit) AS nBalance, '' AS cSMan, B.dDate FROM V_WRRBalanceInquiry_Voucher A " & _
                    "LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
                    "WHERE A.cWRRNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "'"
        Case ToRFPWRR
            sSQL = "SELECT A.*, A.nGross AS nSales, (A.nGross - A.nPaid - A.nReturns + A.nCredit - A.nDebit) AS nBalance, '' AS cSMan, B.dDate FROM V_WRRBalanceInquiry A " & _
                    "LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
                    "WHERE A.cWRRNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "'"
        Case ToVoucherTPC
            sSQL = "SELECT A.*, (A.nSales - A.nPaid - A.nReturns - A.nCredit + A.nDebit) AS nBalance, B.cSMan, B.dDate, C.nTPCGross, (C.nTPCGross - C.nPaid) AS nTPCBalance FROM V_SalesBalanceInquiry A " & _
                    "LEFT OUTER JOIN SALES B ON A.cInvNo = B.cInvNo AND A.cCompanyID = B.cCompanyID " & _
                    "LEFT OUTER JOIN V_TPCBalanceInquiry C ON A.cInvNo = C.cInvNo AND A.cCompanyID = C.cCompanyID " & _
                    "WHERE A.cInvNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "'"
        Case ToIssuance
            sSQL = "SELECT A.*, A.nTDebit - A.nTCredit AS nBalance FROM VOUCHER A " & _
                    "WHERE A.cTranNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & sUnitId & "' AND A.lCancelled = 0 "
        Case ToVoucherPI
            
            sSQL = "SELECT A.*, A.nGross AS nSales, (A.nGross - A.nPaid - A.nReturns + A.nCredit - A.nDebit) AS nBalance, '' AS cSMan, B.dDate, B.nEWT " & _
                    " FROM V_PIBalanceInquiry_Voucher A " & _
                    "    LEFT OUTER JOIN PURCHASE B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID " & _
                    " WHERE A.cTranNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "'"
            'sSQL = "exec sp_PIBalanceInquiry  '" & Trim(InvNo) & "' ,'" & COID & "'"
        Case ToRFPPI
            sSQL = "SELECT A.*, A.nGross AS nSales, (A.nGross - A.nPaid - A.nReturns + A.nCredit - A.nDebit) AS nBalance, '' AS cSMan, B.dDate " & _
                   " FROM V_PIBalanceInquiry A " & _
                   " LEFT OUTER JOIN PURCHASE B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID " & _
                   " WHERE A.cTranNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "'"
'            sSQL = "SELECT A.*, A.nGross AS nSales, (A.nGross - A.nPaid - A.nReturns + A.nCredit - A.nDebit) AS nBalance, '' AS cSMan, B.dDate FROM V_WRRBalanceInquiry A " & _
'                    "LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
'                    "WHERE A.cWRRNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "'"
        Case ToDebitCreditPI_Voucher
'            sSQL = "SELECT A.*, A.nGross AS nSales, (A.nGross - A.nPaid - A.nReturns + A.nCredit - A.nDebit) AS nBalance, '' AS cSMan, B.dDate " & _
                   " FROM V_PIBalanceInquiry_Voucher A " & _
                   " LEFT OUTER JOIN PURCHASE B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID " & _
                   " WHERE A.cTranNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "'"
                   
            sSQL = "exec sp_PIBalanceInquiry  '" & Trim(InvNo) & "' ,'" & COID & "'"
            
        Case ToSOE
            'sSQL = "SELECT A.*, A.nTCredit AS nBalance FROM VOUCHER A " & _
                    "WHERE A.cTranNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "' AND A.lCancelled = 0 "
                    
                    
             sSQL = "exec sp_PMSVoucherBalance  '" & Trim(InvNo) & "' ,'" & COID & "'"
'            sSQL = "SELECT A.*, A.nGross AS nSales, (A.nGross - A.nPaid - A.nReturns + A.nCredit - A.nDebit) AS nBalance, '' AS cSMan, B.dDate FROM V_WRRBalanceInquiry_Voucher A " & _
'                    "LEFT OUTER JOIN WRR B ON A.cWRRNo = B.cWRRNo AND A.cCompanyID = B.cCompanyID " & _
'                    "WHERE A.cWRRNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "'"
        Case Else
            sSQL = "SELECT A.*, (A.nSales - A.nPaid - A.nReturns - A.nCredit + A.nDebit) AS nBalance, B.cSMan, B.dDate, A.nSales AS nGross FROM V_SalesBalanceInquiry A " & _
                    "LEFT OUTER JOIN SALES B ON A.cInvNo = B.cInvNo AND A.cCompanyID = B.cCompanyID " & _
                    "WHERE A.cInvNo = '" & Trim(InvNo) & "' AND A.cCompanyID = '" & COID & "'"
        
    End Select
    
    OpenNewConnection connList
    
    rsList.Open sSQL, connList, adOpenForwardOnly, adLockReadOnly
    
With rsList
            txtSMCode = Trim(!cSMan) & ""
            dtbDate.Text = Format(!dDate, "MM/dd/yyyy")
            mdDate = Format(!dDate, "MM/dd/yyyy")
            txtGross = !nGross
            txtReturns = !nReturns
            txtPayment = !nPaid
            txtDebit = !nDebit
            txtCredit = !nCredit
            txtBalance = !nBalance
            mnBalance = !nBalance
End With
    If mSIPick = ToVoucherTPC Then
        txtGross.Visible = False
        txtReturns.Visible = False
        txtPayment.Visible = False
        txtDebit.Visible = False
        txtCredit.Visible = False
        txtBalance.Visible = False
        txtTPCGross.Visible = True
        txtTPCGross.Label = "TPC Gross"
        txtTPCBalance.Label = "TPC Balance"
        txtTPCGross = rsList!nTPCGross
        txtTPCBalance = rsList!nTPCBalance
        mnTPCBalance = rsList!nTPCBalance
    ElseIf mSIPick = ToIssuance Or mSIPick = ToSOE Then
        dtbDate.Visible = False
        txtGross.Visible = False
        txtReturns.Visible = False
        txtPayment.Visible = False
        txtDebit.Visible = False
        txtCredit.Visible = False
        txtBalance.Visible = False
        txtTPCGross.Visible = True
        txtTPCGross.Label = "Debit"
        txtTPCBalance.Label = "Balance"
        txtTPCGross = rsList!nTDebit
        txtTPCBalance = rsList!nBalance
        mnTPCBalance = rsList!nBalance
        lblClient = rsList!cCode & " - " & GetValueFrTable("cName", "CLIENT_CUSTOMER", "cCode = '" & Trim(rsList!cCode) & "'")
    ElseIf mSIPick = ToVoucherPI Then
    
        txtSMCode = Trim(rsList!cSMan) & ""
        dtbDate.Text = Format(rsList!dDate, "MM/dd/yyyy")
        mdDate = Format(rsList!dDate, "MM/dd/yyyy")
        txtGross = rsList!nSales
        txtReturns = rsList!nReturns
        txtPayment = rsList!nPaid
        txtDebit = rsList!nDebit
        txtCredit = rsList!nCredit
        txtBalance = rsList!nBalance
        mnBalance = rsList!nBalance
        mnEWTrate = rsList!nEWT
    Else
        txtGross.Visible = True
        txtReturns.Visible = True
        txtPayment.Visible = True
        txtDebit.Visible = True
        txtCredit.Visible = True
        txtBalance.Visible = True
        txtTPCGross.Visible = False
    End If
    
    rsList.Close
    Set rsList = Nothing

End Sub

Private Sub lvwList_KeyPress(KeyAscii As Integer)
    If lvwList.ListItems.Count = 0 Then Exit Sub
    If KeyAscii = 13 Then
        lvwList_ItemClick lvwList.ListItems(lvwList.SelectedItem.Index)
        SendKeys "{Tab}"
    End If
End Sub
